const db = require("../model/database");
const sqlQuery = require("../model/query");
const dayjs = require("dayjs");
const path = require("path");
const fs = require("fs");

// 添加学生
exports.addStudent = async (req, res) => {
    const studentInfo = req.body;
    // 如果不存在，就添加
    studentInfo.create_time = dayjs().format("YYYY-MM-DD HH:mm:ss");
    let result = await sqlQuery("INSERT INTO student SET ?", studentInfo, db);
    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "添加成功"
        });
    }
};

// 头像上传
exports.uploadAvatar = async (req, res) => {
    const file = req.file;
    if (file) {
        // 获取后缀
        const ext = path.extname(file.originalname);
        // 重命名
        const random = Math.floor(Math.random() * 9999999);
        let oldName = file.path; //获取path: 'public\\upload\\0f625978d5d1a783b12e149718f8b634',
        let newName = path.join(__dirname, "../public/upload/avatar/" + random + ext);
        fs.renameSync(oldName, newName); //将老的文件名改成新的有后缀的文件 #同步写法
        res.send({
            code: 20000,
            data: "http://fanshu.xyz:8080/uploads/avatar/" + random + ext
        });
    } else {
        res.send({
            code: 40001,
            data: "请选择正确的文件"
        });
    }
};

// 修改学生信息
exports.updateStudent = async (req, res) => {
    const studentInfo = req.body;
    studentInfo.create_time = dayjs(studentInfo.create_time).format("YYYY-MM-DD HH:mm:ss");
    studentInfo.update_time = dayjs(new Date()).format("YYYY-MM-DD HH:mm:ss");
    const sql = `UPDATE student SET ? WHERE id = ${studentInfo.id}`;
    const result = await sqlQuery(sql, studentInfo, db);
    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "修改成功"
        });
    } else {
        res.json({
            code: 40001,
            msg: "修改失败"
        });
    }
};

// 获取学生列表
exports.getStudentList = async (req, res) => {
    const page = req.params.page || 1;
    const pageSize = req.params.pageSize || 10;
    const status = req.params.status || "0";
    let is_delete;
    // 0 是在校生;1是已删除
    if (status === "0") {
        is_delete = 0;
    } else {
        is_delete = 1;
    }
    // 获取查询参数
    const searchObj = req.query;
    // 模糊查询
    const tempSql = res.getSearchSql(searchObj);
    // 范围查询
    let tempSql2 = "";
    if (searchObj["startTime"]) {
        tempSql2 = res.getRangeSql(searchObj);
    }
    // 查询年满三年及以下的学生
    let tempSql3 = "";
    if (status === "0") {
        tempSql3 = ` AND TIMESTAMPDIFF(DAY, student.create_time, CURDATE()) <= 3 * 365`;
    }
    // 合并sql
    const sql = `SELECT * FROM student  where is_delete = ${is_delete} ${tempSql} ${tempSql2} ${tempSql3} LIMIT ${
        (page - 1) * pageSize
    }, ${pageSize}`;
    const sql2 = `SELECT * FROM student where is_delete = ${is_delete} ${tempSql} ${tempSql2} ${tempSql3}`;
    // 执行sql
    let result = await sqlQuery(sql, null, db);
    let result2 = await sqlQuery(sql2, null, db);
    // 返回数据
    if (result.length > 0) {
        res.json({
            code: 20000,
            data: result,
            total: result2.length
        });
    } else {
        res.json({
            code: 20000,
            msg: "没有数据"
        });
    }
};

// 获取学生信息
exports.getStudentInfo = async (req, res) => {
    const id = req.params.id;
    if (id === "undefined") {
        res.json({
            code: 40001,
            msg: "id不能为空"
        });
        return;
    }
    const sql = `SELECT * FROM student WHERE id = ${id} and is_delete = 0`;
    const result = await sqlQuery(sql, null, db);
    if (result.length > 0) {
        res.json({
            code: 20000,
            data: result[0]
        });
    }
};

// 删除学生
exports.deleteStudent = async (req, res) => {
    const id = req.params.id;
    if (id === "undefined") {
        res.json({
            code: 40001,
            msg: "id不能为空"
        });
        return;
    }

    const sql = `UPDATE student SET is_delete = 1 WHERE id = ${id}`;
    const result = await sqlQuery(sql, null, db);
    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "删除成功"
        });
    } else {
        res.json({
            code: 40001,
            msg: "删除失败"
        });
    }
};

// 批量删除学生
exports.deleteStudentBatch = async (req, res) => {
    const ids = req.body;
    if (ids.length === 0) {
        res.json({
            code: 40001,
            msg: "id不能为空, 请选择要删除的学生"
        });
        return;
    }
    const sql = `UPDATE student SET is_delete = 1 WHERE id in (${ids})`;
    const result = await sqlQuery(sql, null, db);
    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "删除成功"
        });
    } else {
        res.json({
            code: 40001,
            msg: "删除失败"
        });
    }
};
